/************************************************************************************/
/*  Program name CAPFNDIMP_REQUEST_SETS.sql                                         */
/*  Creater:  Chan Billy Ka Keung (PCCW - UniHub)                                   */
/*************   History   *************************                                */
/* 01 FEB, 2006   CHAN Billy Ka Keung         First created                         */
/************************************************************************************/

SET SERVEROUTPUT ON SIZE 1000000;
ALTER TRIGGER FND_REQUEST_SETS_CT1 DISABLE;

DECLARE

	lv_Prog_ID		VARCHAR2(100) 	:=	'CAPFNDIMP_REQUEST_SETS';

	lv_Scc_Count	NUMBER			:=	0;
	lv_Err_Count	NUMBER			:=	0;
	lv_Err_Msg		VARCHAR2(500)	:=	'No Error';

	CURSOR Cur_F IS
	SELECT
		*
	FROM
		CRST_FND_REQUEST_SETS;

	CURSOR Cur_G IS
	SELECT
		A.*
	FROM
		CFND_REQUEST_SETS		A,
		CRST_FND_REQUEST_SETS	B
	WHERE	A.Application_ID	=	B.Application_ID
	AND		A.Request_Set_ID	=	B.Request_Set_ID;
--	AND		A.Request_Set_Name	=	B.Request_Set_Name;

	lv_Next_Seq_ID	NUMBER;

BEGIN

	UPDATE
		FND_REQUEST_SETS A
	SET
		REQUEST_SET_NAME	= (	SELECT SUBSTR(CAPBRIMP_MAPPING.RENAMESTR(SUBSTR(DESCRIPTION,1,4),DESCRIPTION),1,4)||REQUEST_SET_NAME
								FROM 	FND_REQUEST_SETS_TL B
								WHERE 	A.Application_ID	=	B.Application_ID
								AND		A.Request_Set_ID	=	B.Request_Set_ID
								AND     B.LANGUAGE = 'US')
	WHERE	A.Request_Set_ID IN
				(
				SELECT
					Request_Set_ID 
				FROM
					CRST_FND_REQUEST_SETS
				);

	FOR C1 IN Cur_F LOOP
		BEGIN
			SELECT	FND_REQUEST_SETS_S.NextVal
			INTO	lv_Next_Seq_ID
			FROM	DUAL;

			INSERT INTO CRST_FND_REQUEST_SETS_MAP
			VALUES
			(
			C1.APPLICATION_ID,
			C1.REQUEST_SET_ID,
			C1.REQUEST_SET_NAME,
			C1.APPLICATION_ID,
			lv_Next_Seq_ID,
			C1.REQUEST_SET_NAME
			);

			INSERT INTO FND_REQUEST_SETS
			VALUES
			(
			C1.APPLICATION_ID,
			lv_Next_Seq_ID,
			C1.REQUEST_SET_NAME,
			C1.LAST_UPDATE_DATE,
			CAPBRIMP_COMMON.GetUser(C1.LAST_UPDATED_BY_NAME),				--LAST_UPDATED_BY,
			C1.CREATION_DATE,
			CAPBRIMP_COMMON.GetUser(C1.CREATED_BY_NAME),					--CREATED_BY,
			C1.LAST_UPDATE_LOGIN,
			C1.ALLOW_CONSTRAINTS_FLAG,
			C1.PRINT_TOGETHER_FLAG,
			C1.START_DATE_ACTIVE,
			C1.START_STAGE,
			C1.END_DATE_ACTIVE,
			CAPBRIMP_COMMON.GetFNDConcPrgID(C1.CONCURRENT_PROGRAM_ID_D),	--CONCURRENT_PROGRAM_ID,
			C1.OWNER,
			C1.PRINTER,
			C1.PRINT_STYLE,
			C1.ICON_NAME
			);
		EXCEPTION
			WHEN OTHERS THEN
				DBMS_OUTPUT.Put_Line(SQLERRM);
				lv_Err_Count	:=	lv_Err_Count + 1;
		END;
		lv_Scc_Count		:= lv_Scc_Count + 1;
	END LOOP;
/*
	BEGIN
		UPDATE	FND_REQUEST_SETS
		SET		Start_Stage		=	CAPBRIMP_MAPPING.GetRequestSetIDMapping(Start_Stage)
		WHERE	Start_Stage		IS	NOT NULL
		AND		Request_Set_ID	IN
					(
						SELECT	Request_Set_ID_N
						FROM	CRST_FND_REQUEST_SETS_MAP
					);
	EXCEPTION
		WHEN OTHERS THEN
			DBMS_OUTPUT.Put_Line(SQLERRM);
			lv_Err_Count	:=	lv_Err_Count + 1;
	END;
*/
	BEGIN
		FOR C2 IN Cur_G LOOP
			
			SELECT	FND_REQUEST_SETS_S.NextVal
			INTO	lv_Next_Seq_ID
			FROM	DUAL;
			
			INSERT INTO CFND_REQUEST_SETS VALUES
			(
				C2.Application_ID,
				lv_Next_Seq_ID,
				CAPBRIMP_MAPPING.GetOrgMapping(C2.S_ORG_ID),
				CAPBRIMP_MAPPING.GetBGMapping(C2.S_BUSINESS_GROUP_ID)
			);
		END LOOP;
	EXCEPTION
		WHEN OTHERS THEN
			DBMS_OUTPUT.Put_Line(SQLERRM);
			lv_Err_Count	:=	lv_Err_Count + 1;
	END;

	IF lv_Err_Count	>	0	THEN
		ROLLBACK;
	END IF;

	INSERT INTO CAPCNV_LOG(
		PROG_ID,
		PROCESS_DATE,
		RECORD_PROCESSED,
		RECORD_REJECTED,
		RECORD_EXTRACTED,
		ERR_MSG
		)
	VALUES (
		lv_Prog_ID,
		SYSDATE,
		lv_Scc_Count,
		lv_Err_Count,
		lv_Scc_Count - lv_Err_Count,
		lv_Err_Msg
	);

	COMMIT;
END;
/
ALTER TRIGGER FND_REQUEST_SETS_CT1 ENABLE;
